////////////////////////////////////////////////////////////////////////////////
// Copyright 2005 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   TODO_LIST
// AUTHOR           :   Victor.zheng 
// DATE CREATE      :   2005/10/25
// DESCRIPTION      :   a DAO file for the function, TODO_LIST
//                  :   
//                  :   
//                  :   
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2005/10/25   Victor.zheng    1.0                 source is generated.
//
//
//
////////////////////////////////////////////////////////////////////////////////

package com.erp.app.login.todoList.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.erp.app.common.ProjectConstants;
import com.erp.app.login.todoList.criteria.TodoListSrchCriteria;
import com.erp.app.login.todoList.pk.TodoListPk;
import com.erp.app.login.todoList.vo.TodoListVo;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.dao.SimplePageEditStyleDao;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomBigDecimal;
import com.erp.framework.types.CustomSqlDate;

/**
 * <p>
 * A <strong>DAO</strong>(Data Access Object) abstracts and encapsulates all access to the persistent store. The DAO manages the
 * connection with the data source to obtain and store data. Also, the DAO implements the access mechanism required to work with
 * the data source, and always provides a uniform API to its clients.
 * 
 * @author Victor.zheng
 * @version 1.0 2005/10/25
 * @since FrameWork 1.0
 * 
 */
public class TodoListDao extends SimplePageEditStyleDao {

    /**
     * Returns the SQL select statement.
     * 
     * @param obj
     *            the searching criteria
     * @return a string containing sql query
     * 
     * @author Victor.zheng
     * @since FrameWork 1.0
     */
    protected String sqlQuery(SimpleBaseSrchCriteria obj) {

        TodoListSrchCriteria criteria = (TodoListSrchCriteria) obj;
        String whereClause = toWhereClause(criteria);
        String groupByClause = "";
        String orderByClause = "ORDER BY COMPANY_CODE, USER_GROUP, TODO_CODE";

        String sql = "SELECT COMPANY_CODE, USER_GROUP, TODO_CODE, TODO_NAME, TODO_DESC, "
                + "STATUS, PRIORITY, CATEGORY, REPETITION, DUE_ON, "
                + "ALERT_ON, POSTPONED_ON, COMPLETED_ON, EST_DURATION, ACTUAL_DURATION, "
                + "NO_DAYS_TO_POSTPONE, ACTIVE_IND, ACTIVE_DATE, INACTIVE_DATE, DC_IND, "
                + "DC_DATE, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE " + "FROM T_TODO_LIST " + whereClause
                + groupByClause + orderByClause;

        //super.logSqlQuery(sql);
        return sql;
    }

    /**
     * Returns the where clause of a SQL select statement.
     * 
     * @param obj
     *            the searching criteria
     * @return a string containing the where clause
     * 
     * @author Victor.zheng
     * @since FrameWork 1.0
     */
    private String toWhereClause(TodoListSrchCriteria crit) {

        TodoListSrchCriteria criteria = (TodoListSrchCriteria) crit;
        StringBuffer whereClause = new StringBuffer();

        if (criteria.getSrchCompanyCode() != null) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append("COMPANY_CODE = '" + criteria.getSrchCompanyCode().trim().toUpperCase() + "' ");
        }

        /*
         * if (criteria.getSrchProgram() != null) { whereClause.append(whereClause.length() == 0 ? " WHERE " : " AND ");
         * whereClause.append(" SALES_CONTRACT_NO IN "); whereClause.append(" (SELECT SALES_CONTRACT_NO FROM
         * T_SALES_CONTRACT_DTL"); if (criteria.getSrchProgram() != null) { whereClause.append(" WHERE PROGRAM ='" +
         * criteria.getSrchProgram().toUpperCase().trim() + "'"); } whereClause.append(")"); } if
         * (criteria.getSrchSalesContractNoFr() != null) { whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
         * whereClause.append("SALES_CONTRACT_NO >= '" + criteria.getSrchSalesContractNoFr().toUpperCase().trim() + "' "); } if
         * (criteria.getSrchSalesContractNoTo() != null) { whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
         * whereClause.append("SALES_CONTRACT_NO <= '" + criteria.getSrchSalesContractNoTo().toUpperCase().trim() + "' "); }
         */

        return whereClause.toString();
    }

    /**
     * Constructs a string of SQL insert statement
     * 
     * @return a string of SQL insert statement
     * 
     * @author Victor.zheng
     * @since FrameWork 1.0
     */
    protected String sqlInsert() {

        String sql = "INSERT INTO T_TODO_LIST (" + "COMPANY_CODE, USER_GROUP, TODO_CODE, TODO_NAME, TODO_DESC, "
                + "STATUS, PRIORITY, CATEGORY, REPETITION, DUE_ON, "
                + "ALERT_ON, POSTPONED_ON, COMPLETED_ON, EST_DURATION, ACTUAL_DURATION, "
                + "NO_DAYS_TO_POSTPONE, ACTIVE_IND, ACTIVE_DATE, INACTIVE_DATE, DC_IND, "
                + "DC_DATE, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE) " + "VALUES (" + "?, ?, ?, ?, ?, "
                + "?, ?, ?, ?, ?, " + "?, ?, ?, ?, ?, " + "?, ?, ?, ?, ?, " + "?, ?, SYSDATE, ?, ?" + ")";

        //super.logSqlQuery(sql);
        return sql;
    }

    /**
     * Sets the values for a SQL insert or update statement.
     * 
     * @param pStmt
     *            the incoming prepared statement
     * @param obj
     *            the incoming VO
     * 
     * @author Victor.zheng
     * @since FrameWork 1.0
     */
    protected void setValuesForInsertAndModify(PreparedStatement pStmt, Object obj, String opType) throws SQLException {

        TodoListVo vo = (TodoListVo) obj;
        int i = 1;

        pStmt.setString(i++, vo.getCompanyCode());
        pStmt.setString(i++, vo.getUserGroup());
        pStmt.setString(i++, vo.getTodoCode());
        setNullableString(pStmt, i++, vo.getTodoName());
        setNullableString(pStmt, i++, vo.getTodoDesc());
        setNullableString(pStmt, i++, vo.getStatus());
        setNullableString(pStmt, i++, vo.getPriority());
        setNullableString(pStmt, i++, vo.getCategory());
        setNullableString(pStmt, i++, vo.getRepetition());
        setNullableDate(pStmt, i++, vo.getDueOn());
        setNullableDate(pStmt, i++, vo.getAlertOn());
        setNullableDate(pStmt, i++, vo.getPostponedOn());
        setNullableDate(pStmt, i++, vo.getCompletedOn());
        setNullableBigDecimal(pStmt, i++, vo.getEstDuration());
        setNullableBigDecimal(pStmt, i++, vo.getActualDuration());
        setNullableBigDecimal(pStmt, i++, vo.getNoDaysToPostpone());
        setNullableString(pStmt, i++, vo.getActiveInd());
        setNullableDate(pStmt, i++, vo.getActiveDate());
        setNullableDate(pStmt, i++, vo.getInactiveDate());
        setNullableString(pStmt, i++, vo.getDcInd());
        setNullableDate(pStmt, i++, vo.getDcDate());
        setNullableString(pStmt, i++, vo.getCreateUser());

        if (ProjectConstants.DB_OP_UPDATE.equals(opType))
            setNullableTimestamp(pStmt, i++, vo.getCreateDate());

        setNullableString(pStmt, i++, vo.getUpdateUser());

        if (ProjectConstants.DB_OP_INSERT.equals(opType))
            setNullableTimestamp(pStmt, i++, vo.getUpdateDate());

        if (ProjectConstants.DB_OP_UPDATE.equals(opType)) {
            pStmt.setString(i++, vo.getCompanyCode());
            pStmt.setString(i++, vo.getUserGroup());
            pStmt.setString(i++, vo.getTodoCode());
            setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
        }
    }

    /**
     * Sets the values for a SQL insert statement.
     * 
     * @param pStmt
     *            the incoming prepared statement
     * @param obj
     *            the incoming VO
     * 
     * @author Victor.zheng
     * @since FrameWork 1.0
     */
    protected void setValuesForInsert(PreparedStatement pStmt, Object obj) throws SQLException {

        setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_INSERT);
    }

    /**
     * Constructs a string of SQL update statement
     * 
     * @return a string of SQL update statement
     * 
     * @author Victor.zheng
     * @since FrameWork 1.0
     */
    protected String sqlUpdate() {

        String sql = "UPDATE T_TODO_LIST SET COMPANY_CODE=?, USER_GROUP=?, TODO_CODE=?, TODO_NAME=?, TODO_DESC=?, "
                + "STATUS=?, PRIORITY=?, CATEGORY=?, REPETITION=?, DUE_ON=?, "
                + "ALERT_ON=?, POSTPONED_ON=?, COMPLETED_ON=?, EST_DURATION=?, ACTUAL_DURATION=?, "
                + "NO_DAYS_TO_POSTPONE=?, ACTIVE_IND=?, ACTIVE_DATE=?, INACTIVE_DATE=?, DC_IND=?, "
                + "DC_DATE=?, CREATE_USER=?, CREATE_DATE=?, UPDATE_USER=?, UPDATE_DATE=SYSDATE "
                + "WHERE COMPANY_CODE=? AND USER_GROUP=? AND TODO_CODE=? " + "AND (UPDATE_DATE IS NULL OR UPDATE_DATE = ?) ";

        //super.logSqlQuery(sql);
        return sql;
    }

    /**
     * Sets the values for a SQL update statement.
     * 
     * @param pStmt
     *            the incoming prepared statement
     * @param obj
     *            the incoming VO
     * 
     * @author Victor.zheng
     * @since FrameWork 1.0
     */
    protected void setValuesForModify(PreparedStatement pStmt, Object obj) throws SQLException {

        setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_UPDATE);
    }

    /**
     * Constructs a string of SQL update statement and set ACTIVE_IND to NO
     * 
     * @return a string of SQL update statement
     * 
     * @author Victor.zheng
     * @since FrameWork 1.0
     */
    protected String sqlDelete() {
        String sql = "UPDATE T_TODO_LIST SET ACTIVE_IND='" + GENERIC_NO
                + "', INACTIVE_DATE=SYSDATE, ACTIVE_DATE=NULL WHERE COMPANY_CODE=? AND USER_GROUP=? AND TODO_CODE=? ";

        //super.logSqlQuery(sql);
        return sql;
    }

    /**
     * Sets the values for a SQL update statement which set the ACTIVE_IND to NO.
     * 
     * @param pStmt
     *            the incoming prepared statement
     * @param obj
     *            the incoming VO
     * 
     * @author Victor.zheng
     * @since FrameWork 1.0
     */
    protected void setValuesForDelete(PreparedStatement pStmt, Object obj) throws SQLException {

        TodoListVo vo = (TodoListVo) obj;
        int i = 1;

        pStmt.setString(i++, vo.getCompanyCode());
        pStmt.setString(i++, vo.getUserGroup());
        pStmt.setString(i++, vo.getTodoCode());
    }

    /**
     * Constructs a string of SQL delete statement
     * 
     * @return a string of SQL delete statement
     * 
     * @author Victor.zheng
     * @since FrameWork 1.0
     */
    protected String sqlPurge() {
        String sql = "DELETE FROM T_TODO_LIST WHERE COMPANY_CODE=? AND USER_GROUP=? AND TODO_CODE=? ";

        //super.logSqlQuery(sql);
        return sql;
    }

    /**
     * Sets the values for a SQL delete statement.
     * 
     * @param pStmt
     *            the incoming prepared statement
     * @param obj
     *            the incoming VO
     * 
     * @author Victor.zheng
     * @since FrameWork 1.0
     */
    protected void setValuesForPurge(PreparedStatement pStmt, Object obj) throws SQLException {

        TodoListVo vo = (TodoListVo) obj;
        int i = 1;

        pStmt.setString(i++, vo.getCompanyCode());
        pStmt.setString(i++, vo.getUserGroup());
        pStmt.setString(i++, vo.getTodoCode());
    }

    /**
     * Constructs a string of SQL select statement
     * 
     * @return a string of SQL select statement
     * 
     * @author Victor.zheng
     * @since FrameWork 1.0
     */
    protected String sqlFind() {

        String sql = "SELECT COMPANY_CODE, USER_GROUP, TODO_CODE, TODO_NAME, TODO_DESC, "
                + "STATUS, PRIORITY, CATEGORY, REPETITION, DUE_ON, "
                + "ALERT_ON, POSTPONED_ON, COMPLETED_ON, EST_DURATION, ACTUAL_DURATION, "
                + "NO_DAYS_TO_POSTPONE, ACTIVE_IND, ACTIVE_DATE, INACTIVE_DATE, DC_IND, "
                + "DC_DATE, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE " + "FROM T_TODO_LIST "
                + "WHERE COMPANY_CODE=? AND USER_GROUP=? AND TODO_CODE=? " + "ORDER BY COMPANY_CODE, USER_GROUP, TODO_CODE";

        //super.logSqlQuery(sql);
        return sql;
    }

    /**
     * Sets the values for a SQL select statement for finding by primary key.
     * 
     * @param pStmt
     *            the incoming prepared statement
     * @param obj
     *            the incoming PK
     * 
     * @author Victor.zheng
     * @since FrameWork 1.0
     */
    protected void setValuesForFind(PreparedStatement pStmt, Object obj) throws SQLException {

        TodoListPk pk = (TodoListPk) obj;
        int i = 1;

        pStmt.setString(i++, pk.getCompanyCode());
        pStmt.setString(i++, pk.getUserGroup());
        pStmt.setString(i++, pk.getTodoCode());
    }

    /**
     * Constructs a string of SQL select statement
     * 
     * @return a string of SQL select statement
     * 
     * @author Victor.zheng
     * @since FrameWork 1.0
     */
    protected String sqlList() {

        return null;
    }

    /**
     * Sets the values for a SQL select statement for finding child tables.
     * 
     * @param pStmt
     *            the incoming prepared statement
     * @param obj
     *            the incoming parent PK
     * 
     * @author Victor.zheng
     * @since FrameWork 1.0
     */
    protected void setValuesForList(PreparedStatement pStmt, Object obj) throws SQLException {

    }

    /**
     * Sets the values of a result set to a vo
     * 
     * @param rs
     *            the incoming result set
     * @return a vo containing the result set values
     * 
     * @author Victor.zheng
     * @since FrameWork 1.0
     */
    protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {

        TodoListVo vo = new TodoListVo();

        vo.setCompanyCode(rs.getString("COMPANY_CODE"));
        vo.setUserGroup(rs.getString("USER_GROUP"));
        vo.setTodoCode(rs.getString("TODO_CODE"));
        vo.setTodoName(rs.getString("TODO_NAME"));
        vo.setTodoDesc(rs.getString("TODO_DESC"));
        vo.setStatus(rs.getString("STATUS"));
        vo.setPriority(rs.getString("PRIORITY"));
        vo.setCategory(rs.getString("CATEGORY"));
        vo.setRepetition(rs.getString("REPETITION"));
        vo.setDueOn(CustomSqlDate.customize(rs.getDate("DUE_ON")));
        vo.setAlertOn(CustomSqlDate.customize(rs.getDate("ALERT_ON")));
        vo.setPostponedOn(CustomSqlDate.customize(rs.getDate("POSTPONED_ON")));
        vo.setCompletedOn(CustomSqlDate.customize(rs.getDate("COMPLETED_ON")));
        vo.setEstDuration(CustomBigDecimal.customize(rs.getBigDecimal("EST_DURATION")));
        vo.setActualDuration(CustomBigDecimal.customize(rs.getBigDecimal("ACTUAL_DURATION")));
        vo.setNoDaysToPostpone(CustomBigDecimal.customize(rs.getBigDecimal("NO_DAYS_TO_POSTPONE")));
        vo.setActiveInd(rs.getString("ACTIVE_IND"));
        vo.setActiveDate(CustomSqlDate.customize(rs.getDate("ACTIVE_DATE")));
        vo.setInactiveDate(CustomSqlDate.customize(rs.getDate("INACTIVE_DATE")));
        vo.setDcInd(rs.getString("DC_IND"));
        vo.setDcDate(CustomSqlDate.customize(rs.getDate("DC_DATE")));
        vo.setCreateUser(rs.getString("CREATE_USER"));
        vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
        vo.setUpdateUser(rs.getString("UPDATE_USER"));
        vo.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));

        return vo;
    }

}
